package com.foreknow.demo14;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @author shkstart
 * @create 2021-08-03 13:43
 */
public class DBUtil {
    public static Connection conn;
    public static Statement stmt;
    public static PreparedStatement pstmt;
    public static ResultSet rs;  //查询
    List<Guestbook> list;


    /**
     * 连接数据库的方法
     */
    public Connection connect() throws ClassNotFoundException, SQLException {
        boolean isSuccess = false;

            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            String url = "jdbc:mysql://localhost:3306/basic5?useSSL=false&serverTimezone=UTC";
            conn = DriverManager.getConnection(url, "root", "123456");
            if (conn != null) {
                isSuccess = true;
            }

        return conn;
    }

    //public DBUtil(){
//    try {
//        pstmt = conn.prepareStatement("select * from guestbook");
//        rs=pstmt.executeQuery();
//        while (rs.next()){
//            Guestbook g1=new Guestbook(rs.getInt("id"),rs.getString("name"),rs.getString("phone"),
//                    rs.getString("email"),rs.getString("title"),rs.getString("content"));
//            list.add(g1);
//        }
//    } catch (SQLException e) {
//        e.printStackTrace();
//    }
//}
    public List<Guestbook> show() {
        list = new ArrayList<>();
        try {
            pstmt = conn.prepareStatement("select * from guestbook");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Guestbook g1 = new Guestbook(rs.getInt("id"), rs.getString("name"), rs.getString("phone"),
                        rs.getString("email"), rs.getString("title"), rs.getString("content"));
                list.add(g1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 查询的方法
     * 根据id查
     */
    public ResultSet search(String sql, Object... valuses) throws SQLException {
//    List<Guestbook> list1=new ArrayList<>();
        //获取到PreparedStatement用于实行动态SQL
            pstmt = conn.prepareStatement(sql);//预处理
            for (int i = 0; i < valuses.length; i++) {
                pstmt.setObject(i + 1, valuses[i]);
            }

            rs = pstmt.executeQuery();


        return rs;

    }

    public void getAll() {
        list = new ArrayList<>();
        try {
            pstmt = conn.prepareStatement("select * from guestbook");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Guestbook g1 = new Guestbook(rs.getInt("id"), rs.getString("name"), rs.getString("phone"),
                        rs.getString("email"), rs.getString("title"), rs.getString("content"));
                list.add(g1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        for (Guestbook gs : list) {
            System.out.println(gs.toString());
        }
    }

    /**
     * DML(增删改操作)   (insert,delete update)
     */
//添加

    //sql=insert  into guestbook
    public void add(String sql, Object... valuses) throws SQLException {
        Scanner sc = new Scanner(System.in);

            //预处理
//            pstmt = conn.prepareStatement(sql + "(name ,phone,email,title,content) values(?,?,?,?,?)");
            pstmt = conn.prepareStatement(sql);

            for (int i = 0; i < valuses.length; i++) {
                pstmt.setObject(i + 1, valuses[i]);
            }
//            System.out.println("输入姓名");
//            String name = sc.next();
//            System.out.println("输入电话");
//            String phone = sc.next();
//            System.out.println("输入email");
//            String email = sc.next();
//            System.out.println("输入title");
//            String title = sc.next();
//            System.out.println("输入content");
//            String cont = sc.next();
//            pstmt.setString(1, name);
//            pstmt.setString(2, phone);
//            pstmt.setString(3, email);
//            pstmt.setString(4, title);
//            pstmt.setString(5, cont);
            //执行预处理
            int i = pstmt.executeUpdate();
    }

    /**
     * 删除某个调数据 根据id删除
     *
     * @param
     */
    public void del(String sql) {
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 修改
     *
     * @param sql
     * @param
     */
    public int update(String sql, Object... values) throws SQLException {
//        Scanner sc = new Scanner(System.in);
//        List<Guestbook> list = new ArrayList<>();
//        List<Guestbook> search = search(id);
//        try {
//            pstmt = conn.prepareStatement(sql);
//            System.out.println("请修改");
//            System.out.println("(" + search.get(0).getId() + "):");
//            int upid = sc.nextInt();
//            System.out.println("(" + search.get(0).getName() + "):");
//            String upname = sc.next();
//            System.out.println("(" + search.get(0).getPhone() + "):");
//            String upphone = sc.next();
//            System.out.println("(" + search.get(0).getEmail() + "):");
//            String upemail = sc.next();
//            System.out.println("(" + search.get(0).getTitle() + "):");
//            String uptitle = sc.next();
//            System.out.println("(" + search.get(0).getContent() + "):");
//            String upcon = sc.next();
//
//
//            pstmt.executeUpdate(sql);
//        } catch (SQLException e) {
//            e.printStackTrace();
//
//        }
//    Guestbook upg=new Guestbook(upid,upname,upphone,upemail,uptitle,upcon);

//    search.get(0).setId(upid);
//    search.get(0).setName(upname);
//    search.get(0).setPhone(upphone);
//    search.get(0).setEmail(upemail);
//    search.get(0).setTitle(uptitle);
//    search.get(0).setContent(upcon);

        pstmt = conn.prepareStatement(sql);
        for (int i = 0; i < values.length; i++) {
            pstmt.setObject(i+1,values[i]);
        }

        int i = pstmt.executeUpdate();

    return i;

    }

    /**
     * 关闭资源
     */

    public void closesorce() {
        try {
            conn.close();
            stmt.close();
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
